import requests
import json
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

#定义URL 就是zabbixAPI接口请求的地址
#从数据库里面获取所有的IP 跟zabbix里面进行对比如果存在就修改hostname 如果不存在就create一台主机

url = 'http://192.168.31.238/api_jsonrpc.php'

#定义mysql的连接信息
cmdbip     ='192.168.31.238'
cmdbport   = 3306
cmdbname   = 'ly'
cmdbpasswd = '123456'
cmdbuser   ='xiangkejin'

#数据库中使用的表
# VMTABLE    ='gsys_admin_vm'
# ROLETABLE  ='gsys_admin_role'
# GAMETABLE  ='gsys_admin_db'
# PORTTABLE  ='gsys_admin_port'


#engine = create_engine('mysql+pymysql://root:08143234@localhost:3306/test?charset=utf8',echo=True)

DB_CONNECT_STRING = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(cmdbuser,cmdbpasswd,cmdbip,cmdbport,cmdbname)
#print(DB_CONNECT_STRING)

# 初始化数据库连接:# echo=True 会打印操作数据库的信息
engine = create_engine(DB_CONNECT_STRING,echo=False)

# 创建DBSession类型:
DB_Session = sessionmaker(bind=engine)

session = DB_Session()

# 获得业务数据库中所有IP
def get_allsqlip():
    sql='select distinct av_vmip from gsys_admin_vm;'
    res=session.execute(sql).fetchall()
    return res

allsqlip=get_allsqlip()
print('ALL MySQL IP:',allsqlip)

# 获得业务数据库中IP对应的主机名
def get_sqlhostname(ip):
    #print(ip)
    sql="select ar_role from gsys_admin_role where ar_vmid=(select sysid from gsys_admin_vm where av_vmip='%s');"  %  (ip)
    ress=session.execute(sql).fetchall()
    result=''
    for i in ress:
        result += i[0]
    return result

#################
## 定义通过HTTP方式访问API地址的函数，后面每次请求API的各个方法都会调用这个函数
def requestJson(url,values):
    post_header = {'Content-Type': 'application/json'}
    data = json.dumps(values)
    ret = requests.post(url, data=data, headers=post_header)
    zabbix_ret = json.loads(ret.content)
    ret.close()
    try:
        #print('Try to get zabbix login token...')
        message = zabbix_ret['result']
        print('OK,Success!')
        #print(message)
        return zabbix_ret['result']
    except:
        message = zabbix_ret['error']['data']
        print('Failed!')
        print(message)


#API接口认证的函数，登录成功会返回一个Token
#大部分的接口认证,请求的时候需要一个token
def authenticate(url):
    post_data = {
        "jsonrpc": "2.0",
        "method": "user.login",
        "params": {
            "user": "Admin",
            "password": "zabbix"
            },
        "id": 0
        }
    token = requestJson(url,post_data)
    return token

auth = authenticate(url)


#通过API接口创建一个主机
def createhost(ip, hostname, auth):
    post_data = {
        "jsonrpc": "2.0",
        "method": "host.create",
        "params": {
            "host": hostname,
            "interfaces": [
                {
                    "type": 1,
                    "main": 1,
                    "useip": 1,
                    "ip": ip,
                    "dns": "",
                    "port": "10050"
                    }
                ],
            "groups": [
                {
                    "groupid": "1"
                    }
                ],
            "templates": [
                {
                    "templateid": "10050"
                    }
                ],
            "inventory_mode": 0,
            "inventory": {
                "macaddress_a": "08:00:27:4f:b1:91"
                }
            },
        "auth": auth,
        "id": 1
        }


    output = requestJson(url, post_data)
    #hostid = output['hostids'][0]
    return output

##测试一下
createhostid = createhost("192.168.1.105","agentd105",auth)


# 通过API接口获取zabbix-server上已有的某IP对应的主机id和hostname
def ipgetHostsinfo(ip,url,auth):
    values ={
              'jsonrpc': '2.0',
              'method': 'host.get',
              'params': {
                  'output': [ "host" ],
                  'filter': {
                      'ip': ip
                  }
              },
              'auth': auth,
              'id': 2
              }
    output = requestJson(url,values)
    return output

#测试代码
#ip1='192.168.1.104'
#hostinfo=ipgetHostsinfo(ip1,url,auth)


# 用API接口获取所有在zabbix里面监控的主机
def getallzabbixip(url, auth):
    values = {
                'jsonrpc': '2.0',
                'method': 'host.get',
                'params': {
                  'output': ["host"],
                  "selectInterfaces": ["ip"]
              },
              'auth': auth,
              'id': 3
              }
    output = requestJson(url, values)
    return output

#测试代码
all_zabbix_ip=getallzabbixip(url,auth)
##print ('-------->')
##print (all_zabbix_ip)
##
##
##print ('-------->')
##for fiteip in all_zabbix_ip:
##     print(fiteip['interfaces'][0]['ip'])



#通过API接口更新主机的hostname
def updatehostname(hostid, hostname, url, auth):
    values = {'jsonrpc': '2.0',
              'method': 'host.update',
              'params': {
                  "hostid": hostid,
                    "name": hostname,
              },
              'auth': auth,
              'id': 4
              }

    output = requestJson(url, values)
    #return output

#测试代码：（配置文件中已将machine1改为agentd104）
#hostinfo=ipgetHostsinfo(ip1,url,auth)
#updatehostname(hostinfo[0]['hostid'], 'agentd104', url, auth)



# 通过API接口来添加新用户
def addnewuser(auth,username,password):
    values = {
    "jsonrpc": "2.0",
    "method": "user.create",
    "params": {
        "alias": username,
        "passwd": password,
        "usrgrps": [
            {
                "usrgrpid": "8"#7-Admin 8-Guests
            }
        ],
        "user_medias": [
            {
                "mediatypeid": "1",
                "sendto": "467782955@qq.com",
                "active": '0',
                "severity": '63',
                "period": "1-7,00:00-24:00"
            }
        ]
    },
    "auth": auth,
    'id': 5
    }
    output = requestJson(url, values)
    return output

##测试
##username='developer111'
##password='123456'
##userid = addnewuser(auth,username,password)
##

#获取所有的告警信息
def getproblem(url,auth):
    values = {
        "jsonrpc": "2.0",
        "method": "problem.get",
        "params": {
            "output": ["extend"],
            "selectAcknowledges": "extend",
            },
        "auth": auth,
        "id": 8
        }
    output = requestJson(url, values)
    return output

def getalert(url,auth):
    values = {
        "jsonrpc": "2.0",
        "method": "alert.get",
        "params": {
            "output": "extend",
            },
        "auth": auth,
        "id": 9
        }
    output = requestJson(url, values)
    return output

##getporf=getproblem(url,auth)
##print('Problems:',getporf)
##getalertaa=getalert(url,auth)
##print('Alerts:',getalertaa)

print ('-------->')

for fiteip in all_zabbix_ip:
    ip=fiteip['interfaces'][0]['ip']
    hostgetinfo = ipgetHostsinfo(ip, url, auth)
    print ('---------->')
    print(hostgetinfo)
    if hostgetinfo:
        hostname = get_sqlhostname(ip)#取业务库中新的主机名
        print('New Host Name:',hostname)

        for i in hostgetinfo:
            hostid = i['hostid']
            print (hostid)

        updatehostname(hostid,hostname,url,auth)